package com.sunwayworld.basemodule.business.batch.io;

import com.sunwayworld.baseline.framework.utils.TemplateExcelHelper;
import com.sunwayworld.basemodule.basedata.batchtype.bean.LimsBatchCategoryBean;
import com.sunwayworld.basemodule.basedata.batchtype.service.LimsBatchCategoryService;
import com.sunwayworld.basemodule.basedata.exporttemplate.service.LimsGenericTemplateService;
import com.sunwayworld.basemodule.basedata.sampletype.bean.LimsSampleTypeBean;
import com.sunwayworld.basemodule.basedata.sampletype.service.LimsSampleTypeService;
import com.sunwayworld.basemodule.coredata.product.bean.LimsProductMainBean;
import com.sunwayworld.basemodule.coredata.product.service.LimsProductMainService;
import com.sunwayworld.basemodule.rm.client.bean.LimsClientBean;
import com.sunwayworld.basemodule.rm.client.service.LimsClientService;
import com.sunwayworld.framework.context.LocalContextHelper;
import com.sunwayworld.framework.exception.InvalidDataException;
import com.sunwayworld.framework.exception.checked.CheckedException;
import com.sunwayworld.framework.i18n.I18nHelper;
import com.sunwayworld.framework.io.excel.utils.ExcelUtils;
import com.sunwayworld.framework.mybatis.mapper.MapperParameter;
import com.sunwayworld.framework.mybatis.mapper.MatchPattern;
import com.sunwayworld.framework.mybatis.mapper.SearchFilter;
import com.sunwayworld.framework.spring.annotation.GikamBean;
import com.sunwayworld.framework.utils.ServletUtils;
import com.sunwayworld.module.sys.code.service.CoreCodeService;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Component;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Path;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Component(value = "LimsBatchExportTemplate")
@GikamBean
public class LimsBatchExportTemplate implements LimsGenericTemplateService<String> {

    @Autowired
    @Lazy
    CoreCodeService coreCodeService;

    @Autowired
    @Lazy
    LimsSampleTypeService limsSampleTypeService;

    @Autowired
    @Lazy
    LimsClientService limsClientService;

    @Autowired
    @Lazy
    LimsProductMainService limsProductMainService;

    @Autowired
    @Lazy
    LimsBatchCategoryService limsBatchCategoryService;

    // 标题行所在索引
    private static final int titleRowIndex = 2;

    @Override
    public void initTemplateData(Path path, String id) {
        try {
            InputStream in = new FileInputStream(path.toString());
            Workbook workBook = WorkbookFactory.create(in);
            Sheet sheet = workBook.getSheetAt(0);

            // 根据选择内容填充下拉框
            String[] columns = new String[]{"gender", "ageUnit"};
            String[] codeCategoryIds = new String[]{"gender", "ageUnit"};
            TemplateExcelHelper.fillSheetSelect(sheet, titleRowIndex, columns, codeCategoryIds);

            Map<String, List<String>> paramMap = new HashMap<>();

            List<LimsClientBean> clientBeans = limsClientService.selectListByFilter(SearchFilter.instance()
                .match("clientStatus", "1").filter(MatchPattern.EQ)
                .match("personId", LocalContextHelper.getLoginUserId()).filter(MatchPattern.CISC));
            if (clientBeans.isEmpty()) {
                throw new InvalidDataException(I18nHelper.getMessage("NO_CUSTOMER_CAN_CHOOSE"));
            }
            paramMap.put("customerName", clientBeans.stream().map(LimsClientBean::getClientName).collect(Collectors.toList()));

            MapperParameter parameter = new MapperParameter();
            parameter.put("filter_derivativeType", "0");
            List<LimsSampleTypeBean> typeBeans = limsSampleTypeService.selectList(parameter);
            paramMap.put("sampleType", typeBeans.stream().map(LimsSampleTypeBean::getSampleTypeName).collect(Collectors.toList()));

            // 将batchType存放到数据行(标题行行数+1)第一行的第二个格子，导入时读取
            Row dataRow1 = sheet.createRow(titleRowIndex + 1);
            Cell batchTypeCell = dataRow1.createCell(1);
            String batchType = ServletUtils.getCurrentRequest().getParameter("batchType");
            batchTypeCell.setCellValue(batchType);

            CellStyle centerCellStyle = ExcelUtils.getCenterCellStyle(workBook);
            centerCellStyle.setBorderTop(BorderStyle.THIN);
            centerCellStyle.setBorderBottom(BorderStyle.THIN);

            // 根据订单类型取普检或者特检的检测项目
            LimsBatchCategoryBean categoryBean = limsBatchCategoryService.selectFirstByFilter(SearchFilter.instance().match("batchTypeCode", batchType).filter(MatchPattern.EQ));
            Sheet productSheet = workBook.getSheetAt(1);
            List<LimsProductMainBean> productMainBeans = limsProductMainService.selectListByFilter(SearchFilter.instance().match("status", "1").filter(MatchPattern.EQ).match("type", categoryBean.getBatchCategory()).filter(MatchPattern.EQ));
            for (int i = 0; i < productMainBeans.size(); i++) {
                Row productNameRow = productSheet.createRow(i + 1);
                Cell cell = productNameRow.createCell(0);
                cell.setCellStyle(centerCellStyle);
                cell.setCellValue(productMainBeans.get(i).getProductName());
            }
            paramMap.put("productName", productMainBeans.stream().map(LimsProductMainBean::getProductName).collect(Collectors.toList()));

            // 新增检测项目下拉，每个样本仅能选择的唯一的检测项目
            fillDropDown(sheet, titleRowIndex, paramMap);

            Sheet clientSheet = workBook.getSheetAt(2);
            for (int i = 0; i < clientBeans.size(); i++) {
                Row productNameRow = clientSheet.createRow(i + 1);

                Cell nameCell = productNameRow.createCell(0);
                nameCell.setCellStyle(centerCellStyle);
                nameCell.setCellValue(clientBeans.get(i).getClientName());

                Cell departmentCell = productNameRow.createCell(1);
                departmentCell.setCellStyle(centerCellStyle);
                departmentCell.setCellValue(clientBeans.get(i).getOffice());
            }

            in.close();
            FileOutputStream outputStream = new FileOutputStream(path.toString());
            workBook.write(outputStream);
            outputStream.close();
            workBook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据下拉内容填充下拉框（可以是非选择内容）
     *
     * @param sheet
     * @param rowIndex      标题行
     * @param selectListMap 填充的数据
     */
    public static void fillDropDown(Sheet sheet, int rowIndex, Map<String, List<String>> selectListMap) {
        int endRowNum = sheet.getPhysicalNumberOfRows()-1;
        Row firstRow = sheet.getRow(rowIndex);
        int endCellNum = firstRow.getPhysicalNumberOfCells();
        //获取列信息：
        String[] columnNames = new String[endCellNum];
        for (int i = 0; i < endCellNum; i++) {
            Cell cell = firstRow.getCell(i);
            if (cell != null) {
                if (cell.getCellComment() != null) {
                    columnNames[i] = cell.getCellComment().getString().getString();
                } else {
                    columnNames[i] = "";
                }
            } else {
                columnNames[i] = "";
            }
        }
        //设置下拉框
        for (int i = 0; i < columnNames.length; i++) {
            String columnName = columnNames[i];
            if (selectListMap.containsKey(columnName)) {
                String[] strs = selectListMap.get(columnName).toArray(new String[0]);
                DataValidationHelper helper = sheet.getDataValidationHelper();
                DataValidationConstraint constraint = helper.createExplicitListConstraint(strs);
                //参数顺序：开始行、结束行、开始列、结束列
                CellRangeAddressList regions = new CellRangeAddressList(endRowNum, endRowNum + 300, i, i);
                DataValidation validation = helper.createValidation(constraint, regions);
                //适配xls和xlsx
                if (validation instanceof HSSFDataValidation) {
                    validation.setSuppressDropDownArrow(false);
                } else {
                    validation.setSuppressDropDownArrow(true);
                    validation.setShowErrorBox(true);
                }
                sheet.addValidationData(validation);
            }
        }
    }

}
